package com.xiaozhi.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xiaozhi.entity.HealthAnalytics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 健康数据分析 Mapper 接口
 *
 * @author xiaozhi
 * @since 2024-01-15
 */
@Mapper
public interface HealthAnalyticsMapper extends BaseMapper<HealthAnalytics> {

    /**
     * 根据用户ID分页查询健康分析记录
     *
     * @param page   分页参数
     * @param userId 用户ID
     * @return 分页结果
     */
    @Select("SELECT * FROM health_analytics WHERE user_id = #{userId} AND deleted = 0 ORDER BY created_at DESC")
    IPage<HealthAnalytics> selectPageByUserId(Page<HealthAnalytics> page, @Param("userId") Long userId);

    /**
     * 根据用户ID和分析类型查询
     *
     * @param userId       用户ID
     * @param analysisType 分析类型
     * @return 分析记录列表
     */
    @Select("SELECT * FROM health_analytics WHERE user_id = #{userId} AND analysis_type = #{analysisType} AND deleted = 0 ORDER BY created_at DESC")
    List<HealthAnalytics> selectByUserIdAndType(@Param("userId") Long userId, @Param("analysisType") String analysisType);

    /**
     * 根据风险等级查询分析记录
     *
     * @param page      分页参数
     * @param riskLevel 风险等级
     * @return 分页结果
     */
    @Select("SELECT * FROM health_analytics WHERE risk_level = #{riskLevel} AND deleted = 0 ORDER BY created_at DESC")
    IPage<HealthAnalytics> selectPageByRiskLevel(Page<HealthAnalytics> page, @Param("riskLevel") String riskLevel);

    /**
     * 获取用户最新的健康分析记录
     *
     * @param userId 用户ID
     * @param limit  限制数量
     * @return 最新分析记录列表
     */
    @Select("SELECT * FROM health_analytics WHERE user_id = #{userId} AND deleted = 0 ORDER BY created_at DESC LIMIT #{limit}")
    List<HealthAnalytics> selectRecentByUserId(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 根据时间范围查询健康分析记录
     *
     * @param userId    用户ID
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @return 分析记录列表
     */
    @Select("SELECT * FROM health_analytics WHERE user_id = #{userId} AND created_at BETWEEN #{startDate} AND #{endDate} AND deleted = 0 ORDER BY created_at DESC")
    List<HealthAnalytics> selectByDateRange(@Param("userId") Long userId, 
                                          @Param("startDate") LocalDateTime startDate, 
                                          @Param("endDate") LocalDateTime endDate);

    /**
     * 统计用户各类型分析记录数量
     *
     * @param userId 用户ID
     * @return 统计结果
     */
    @Select("SELECT analysis_type, COUNT(*) as count FROM health_analytics WHERE user_id = #{userId} AND deleted = 0 GROUP BY analysis_type")
    List<Map<String, Object>> countByAnalysisType(@Param("userId") Long userId);

    /**
     * 统计用户各风险等级记录数量
     *
     * @param userId 用户ID
     * @return 统计结果
     */
    @Select("SELECT risk_level, COUNT(*) as count FROM health_analytics WHERE user_id = #{userId} AND deleted = 0 GROUP BY risk_level")
    List<Map<String, Object>> countByRiskLevel(@Param("userId") Long userId);

    /**
     * 获取用户健康评分趋势
     *
     * @param userId    用户ID
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @return 健康评分趋势数据
     */
    @Select("SELECT DATE(created_at) as date, AVG(health_score) as avg_score, MIN(health_score) as min_score, MAX(health_score) as max_score " +
            "FROM health_analytics WHERE user_id = #{userId} AND created_at BETWEEN #{startDate} AND #{endDate} AND deleted = 0 " +
            "GROUP BY DATE(created_at) ORDER BY date")
    List<Map<String, Object>> getHealthScoreTrend(@Param("userId") Long userId, 
                                                 @Param("startDate") LocalDateTime startDate, 
                                                 @Param("endDate") LocalDateTime endDate);

    /**
     * 获取用户未读的高风险分析记录
     *
     * @param userId 用户ID
     * @return 未读高风险记录列表
     */
    @Select("SELECT * FROM health_analytics WHERE user_id = #{userId} AND risk_level IN ('HIGH', 'CRITICAL') AND is_read = 0 AND deleted = 0 ORDER BY created_at DESC")
    List<HealthAnalytics> selectUnreadHighRisk(@Param("userId") Long userId);

    /**
     * 获取系统整体健康统计数据
     *
     * @return 统计数据
     */
    @Select("SELECT " +
            "COUNT(*) as total_analyses, " +
            "COUNT(DISTINCT user_id) as total_users, " +
            "AVG(health_score) as avg_health_score, " +
            "SUM(CASE WHEN risk_level = 'HIGH' OR risk_level = 'CRITICAL' THEN 1 ELSE 0 END) as high_risk_count " +
            "FROM health_analytics WHERE deleted = 0")
    Map<String, Object> getSystemHealthStats();

    /**
     * 获取用户最新的各类型分析记录
     *
     * @param userId 用户ID
     * @return 最新各类型分析记录
     */
    @Select("SELECT ha1.* FROM health_analytics ha1 " +
            "INNER JOIN (" +
            "    SELECT analysis_type, MAX(created_at) as max_created_at " +
            "    FROM health_analytics " +
            "    WHERE user_id = #{userId} AND deleted = 0 " +
            "    GROUP BY analysis_type" +
            ") ha2 ON ha1.analysis_type = ha2.analysis_type AND ha1.created_at = ha2.max_created_at " +
            "WHERE ha1.user_id = #{userId} AND ha1.deleted = 0")
    List<HealthAnalytics> selectLatestByType(@Param("userId") Long userId);

    /**
     * 根据趋势方向查询分析记录
     *
     * @param userId          用户ID
     * @param trendDirection  趋势方向
     * @return 分析记录列表
     */
    @Select("SELECT * FROM health_analytics WHERE user_id = #{userId} AND trend_direction = #{trendDirection} AND deleted = 0 ORDER BY created_at DESC")
    List<HealthAnalytics> selectByTrendDirection(@Param("userId") Long userId, @Param("trendDirection") String trendDirection);

    /**
     * 获取用户月度健康分析汇总
     *
     * @param userId 用户ID
     * @param year   年份
     * @param month  月份
     * @return 月度汇总数据
     */
    @Select("SELECT " +
            "analysis_type, " +
            "COUNT(*) as count, " +
            "AVG(health_score) as avg_score, " +
            "MAX(health_score) as max_score, " +
            "MIN(health_score) as min_score " +
            "FROM health_analytics " +
            "WHERE user_id = #{userId} AND YEAR(created_at) = #{year} AND MONTH(created_at) = #{month} AND deleted = 0 " +
            "GROUP BY analysis_type")
    List<Map<String, Object>> getMonthlySummary(@Param("userId") Long userId, 
                                              @Param("year") Integer year, 
                                              @Param("month") Integer month);

    /**
     * 批量标记为已读
     *
     * @param ids 分析记录ID列表
     * @return 更新数量
     */
    @Select("UPDATE health_analytics SET is_read = 1, updated_at = NOW() WHERE id IN (${ids}) AND deleted = 0")
    int batchMarkAsRead(@Param("ids") String ids);

    /**
     * 删除过期的分析记录
     *
     * @param beforeDate 过期时间点
     * @return 删除数量
     */
    @Select("UPDATE health_analytics SET deleted = 1, updated_at = NOW() WHERE created_at < #{beforeDate} AND deleted = 0")
    int deleteExpiredRecords(@Param("beforeDate") LocalDateTime beforeDate);

    /**
     * 获取用户健康改善趋势统计
     *
     * @param userId    用户ID
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @return 改善趋势统计
     */
    @Select("SELECT " +
            "trend_direction, " +
            "COUNT(*) as count, " +
            "AVG(health_score) as avg_score " +
            "FROM health_analytics " +
            "WHERE user_id = #{userId} AND created_at BETWEEN #{startDate} AND #{endDate} AND deleted = 0 " +
            "GROUP BY trend_direction")
    List<Map<String, Object>> getImprovementTrend(@Param("userId") Long userId, 
                                                 @Param("startDate") LocalDateTime startDate, 
                                                 @Param("endDate") LocalDateTime endDate);
}